USE `ft1`;
DROP procedure IF EXISTS `ValidateUser`;

DELIMITER $$
USE `ft1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ValidateUser`(IN p_email varchar(200),IN p_pass varchar(45))
BEGIN
 
DECLARE statusCode varchar(2);

if p_email in (select `email` from `users`) then

if p_email in (select `email` from `users` WHERE (`email`= p_email AND `password` = MD5(p_pass) )) then

set statusCode = '0';

SELECT
`userId`,`fullName`,`email`,`registrationDt`,`lastlogin` ,statusCode
FROM `users` 
WHERE (`email`= p_email AND `password` = MD5(p_pass) );

UPDATE `users`
SET `lastlogin` = CURRENT_TIMESTAMP
WHERE `email`= p_email;

else 

set statusCode = '2';

SELECT statusCode; 

end if;



else

set statusCode = '1';
SELECT statusCode; 

end if;

END
$$

DELIMITER ;

